﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using IPTV.DBUtility;
using IPTV.module;
using System.Data;

/// <summary>
/// Summary description for PlateDao
/// </summary>
namespace IPTV.dao
{
    public class PlateDao
    {
        public PlateDao()
        {
            
        }

        public List<PlateDto> findPlatByGradeSubjectKind(string gradeName , string subjectId ,string kind)
        {

            String sql = " SELECT p.id  , p.name FROM me_tgsg_s_plate p , grade g "
                        + " WHERE p.publishstate = 1 "
                        + " AND p.grade = g.id "
                        + " AND p.subject = '"+ subjectId + "'"
                        + " AND p.kind = '" + kind +"'"
                        + " AND g.name = '"+ gradeName +"'"
                        + " ORDER BY p.displayorder";

            List<PlateDto> plateList = new List<PlateDto>();
            try
            {
                MySqlDataReader reader = MySQLHelper.ExecuteReader(MySQLHelper.CONNECTION_STRING, sql, null);

                while (reader.Read())
                {
                    PlateDto plate = new PlateDto();
                    plate.Id = Convert.ToInt32(reader[0]);
                    plate.Name = reader[1].ToString();
                    plateList.Add(plate);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("findPlatByGradeSubjectKind run error.");
            }

            return plateList;
        }


        public List<PlateDto> findPlatByGradeNameSubjectNameKind(string gradeName, string subjectName, string kind)
        {

            string sql = " SELECT p.id  , p.name ,  p.icon  ,s.id , s.name  FROM me_tgsg_s_plate p , grade g , subject s  "
            + " WHERE p.publishstate = 1  AND p.grade = g.id  AND p.subject = s.id  "
            + " AND p.kind =@kind AND g.name = @gradeName AND s.name =  @subjectName ORDER BY p.displayorder";

            MySqlParameter[] parms = new MySqlParameter[] 
                { 
                    new MySqlParameter( "@kind" , MySqlDbType.VarChar, 20 ), 
                    new MySqlParameter( "@gradeName" , MySqlDbType.VarChar, 36 ), 
                    new MySqlParameter( "@subjectName" , MySqlDbType.VarChar, 36 )
                };

            parms[0].Value = kind;
            parms[1].Value = gradeName;
            parms[2].Value = subjectName;

            List<PlateDto> plateList = new List<PlateDto>();
            try
            {
                MySqlDataReader reader = MySQLHelper.ExecuteReader(MySQLHelper.CONNECTION_STRING, sql, parms);

                while (reader.Read())
                {
                    PlateDto plate = new PlateDto();
                    plate.Id = Convert.ToInt32(reader[0]);
                    plate.Name = reader[1].ToString();
                    plate.Icon = reader[2].ToString();
                    plate.Subject = reader[3].ToString();
                    plate.SubjectName = reader[4].ToString();   
                    plateList.Add(plate);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("findPlatByGradeNameSubjectNameKind run error.");
            }

            return plateList;

        }

        public List<CourseDto> findCourseByPlateId(int  plateId)
        {

            String sql = " SELECT  v.video_id , TRIM(v.video_name) , v.video_length ,  TRIM(v.keyword) , "
                        + " TRIM(v.cat_name) , v.picTwo , TRIM(v.remark) , TRIM(t.UserName) , t.id "
                        + " FROM  me_tgsg_plate_video pv , cms_video_ext v ,  teacher t "
                        + " WHERE pv.plateid = '" + plateId.ToString() +"'"
                            + " AND pv.publishstate = 1"
                            + " AND pv.videoid = v.video_id"
                            + " AND t.id = v.teacher_id ";


            List<CourseDto> courseList = new List<CourseDto>();
            try
            {
                MySqlDataReader reader = MySQLHelper.ExecuteReader(MySQLHelper.CONNECTION_STRING, sql, null);

                while (reader.Read())
                {
                    CourseDto course = new CourseDto();
                    course.VideoId = reader[0].ToString();
                    course.VideoName = reader[1].ToString();
                    course.VideoLength = reader[2].ToString();
                    course.Keyword = reader[3].ToString();
                    course.CatName = reader[4].ToString();
                    course.PicTwo = reader[5].ToString();
                    course.Remark = reader[6].ToString();
                    course.TeacherName = reader[7].ToString();
                    course.TeacherId = reader[8].ToString();
                    courseList.Add(course);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("findCourseByPlateId run error.");
            }

            return courseList;
        }

    }
}